forum

Home / DeveloperSection / Forums / How to list all tables in a schema having specific column in oracle SQL?

Ask Question
Utpal Vishwas

Utpal Vishwas

Follow
I am Utpal Vishwas from Uttar Pradesh. Have completed my B. Tech. course from MNNIT campus Prayagraj in 2022. I have good knowledge of computer networking.

Can you answer this question?

Answer

1 Answers

Aryan Kumar

Aryan Kumar

18-Jul-2023

Sure, here is how you can list all tables in a schema having specific column in Oracle SQL:

SQL

SELECT table_name
FROM user_tables
WHERE EXISTS (
    SELECT *
    FROM user_tab_cols
    WHERE table_name = user_tables.table_name
    AND column_name = 'column_name'
);

This query will first select all tables from the user_tables view. The user_tables view is a data dictionary view that contains information about all of the tables in the schema that the user is currently logged in to.

The next part of the query uses the EXISTS clause to check if each table in the user_tables view has the specified column. The EXISTS clause takes a subquery as its argument. The subquery in this case selects all columns from the user_tab_cols view where the table name is equal to the table name in the user_tables view and the column name is equal to the specified column.

If the subquery returns any rows, then the EXISTS clause will return TRUE. This means that the table in the user_tables view has the specified column.

The final part of the query selects the table_name column from the user_tables view for all tables that have the specified column.

To run this query, you will need to connect to the Oracle database using a tool like SQLPlus. Once you are connected, you can run the query by typing it into the SQLPlus prompt.

For example, to run the query above, you would type the following into the SQL*Plus prompt:

SQL

SELECT table_name
FROM user_tables
WHERE EXISTS (
    SELECT *
    FROM user_tab_cols
    WHERE table_name = user_tables.table_name
    AND column_name = 'column_name'
);

This would return a list of all tables in the schema that the user is currently logged in to that have the specified column.

MindStick Training

Liked By